"""
Case Type   : Ubtree
Case Name   : alter ubtree索引(PCR)_修改索引分区的所属表空间
Create At   : 2025-11-13
Owner       : sungang14
Description :
    1、创建表空间，建表
    2、建索引时指定PCR
    3、查看索引类型
    4、插入数据
    5、修改索引分区的所属表空间
    6、查询数据
    7、查询计划
    8、清理环境
Expect      :
    1、成功
    2、成功
    3、ustore pcr索引
    4、成功
    5、修改成功至新的表空间
    6、成功
    7、走indexscan
    8、成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class UstoreUbtreeCase0066(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0068'
        self.tablespace_name01 = 'ts_ubtree_0068_01'
        self.tablespace_name02 = 'ts_ubtree_0068_02'
        self.tablespace_name03 = 'ts_ubtree_0068_03'
        self.index_name = 'i_ubtree_0068'
        self.prepare_data_01 = f"""
                                drop table if exists {self.table_name} cascade;
                                drop tablespace if exists {self.tablespace_name01};
                                drop tablespace if exists {self.tablespace_name02};
                                drop tablespace if exists {self.tablespace_name03};
                                create tablespace {self.tablespace_name01} relative location 'tablespace1/{self.tablespace_name01}';
                                create tablespace {self.tablespace_name02} relative location 'tablespace1/{self.tablespace_name02}';
                                create tablespace {self.tablespace_name03} relative location 'tablespace1/{self.tablespace_name03}';
                                create table {self.table_name} (
                                        sale_id int primary key,
                                        sale_date date not null,
                                        amount numeric(10,2)
                                ) with (storage_type=ustore)
                                tablespace {self.tablespace_name01}
                                partition by range (sale_date)
                                (
                                        partition p_202501 values less than ('2025-02-01'),
                                        partition p_202502 values less than ('2025-03-01'),
                                        partition p_202503 values less than (maxvalue)  
                                )
                                enable row movement;"""
        self.prepare_data_02 = f"""
            create index {self.index_name} on {self.table_name} using ubtree(sale_date) local
            (
                partition p_202501_sale_date_idx,
                partition p_202502_sale_date_idx tablespace {self.tablespace_name03},
                partition p_202503_sale_date_idx
            ) with (index_type='pcr')
            tablespace {self.tablespace_name02};"""
        self.prepare_data_03_01 = f"select relkind, reloptions from pg_class where relname like '{self.index_name}%';"
        self.prepare_data_03_02 = f"select relname, parttype, reltablespace from pg_partition where parentid = (select oid from pg_class where relname = '{self.index_name}') order by relname;"
        self.prepare_data_04 = f"insert into {self.table_name} values (1, '2025-03-01', 150.50), (2, '2025-12-15', 200.00), (3, '2025-01-15', 300.00), (4, '2026-05-20', 500.00);"
        self.prepare_data_05 = f"alter index if exists {self.index_name} move partition p_202502_sale_date_idx tablespace {self.tablespace_name01};"
        self.prepare_data_06 = f"select * from {self.table_name} where sale_date = '2025-03-01';"
        self.prepare_data_07 = f"set enable_seqscan = off;" \
                               f"explain (costs off) select * from {self.table_name} where sale_date = '2025-03-01';"
        self.prepare_data_08 = f"drop table if exists {self.table_name} cascade;" \
                               f"drop tablespace if exists {self.tablespace_name01};" \
                               f"drop tablespace if exists {self.tablespace_name02};" \
                               f"drop tablespace if exists {self.tablespace_name03};" \
                               f"set enable_seqscan = on;"

    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertEqual(data_cmd.count(self.constant.TABLESPCE_DROP_SUCCESS), 3, '执行失败' + text)
        self.assertEqual(data_cmd.count(self.constant.TABLESPCE_CREATE_SUCCESS), 3, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:查看索引类型;expect:ustore pcr索引成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_01)
        logger.info(data_cmd)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)
        data_cmd_01 = self.comsh.execut_db_sql(self.prepare_data_03_02)
        logger.info(data_cmd_01)
        self.assertIn(f"p_202501_sale_date_idx", data_cmd_01, '执行失败' + text)

        text = '--step4:插入数据expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step5:修改分区表索引的表空间;expect:修改成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(self.constant.ALTER_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        data_cmd_02 = self.comsh.execut_db_sql(self.prepare_data_03_02)
        logger.info(data_cmd_02)
        self.assertEqual(data_cmd_01.splitlines()[-2].strip(), data_cmd_02.splitlines()[-2].strip(), '执行失败' + text)
        self.assertNotEqual(data_cmd_01.splitlines()[-3].strip(), data_cmd_02.splitlines()[-3].strip(),
                            '执行失败' + text)
        self.assertEqual(data_cmd_01.splitlines()[-4].strip(), data_cmd_02.splitlines()[-4].strip(), '执行失败' + text)

        text = '--step6:查询数据;expect:执行成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn("1 row", data_cmd, '执行失败' + text)

        text = '--step7:查询计划;expect:走indexscan--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertIn(f"Partitioned Bitmap Heap Scan on {self.table_name}", data_cmd, '执行失败' + text)
        self.assertIn(f"Partitioned Bitmap Index Scan on {self.index_name}", data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertEqual(data_cmd.count(self.constant.TABLESPCE_DROP_SUCCESS), 3, '执行失败' + text)
        self.assertIn(f"SET", data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
